<!DOCTYPE html>












  


<html class="theme-next mist use-motion" lang="en">
<head>
  <meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width,initial-scale=1,maximum-scale=2">
<meta name="theme-color" content="#222">






  
  
    
    
  <script src="/lib/pace/pace.min.js?v=1.0.2"></script>
  <link href="/lib/pace/pace-theme-minimal.min.css?v=1.0.2" rel="stylesheet">







<meta http-equiv="Cache-Control" content="no-transform">
<meta http-equiv="Cache-Control" content="no-siteapp">






















<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css">

<link href="/css/main.css?v=6.3.0" rel="stylesheet" type="text/css">


  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png?v=6.3.0">


  <link rel="icon" type="image/png" sizes="32x32" href="/images/imagesfavicon.ico?v=6.3.0">


  <link rel="icon" type="image/png" sizes="16x16" href="/images/imagesfavicon.ico?v=6.3.0">


  <link rel="mask-icon" href="/images/logo.svg?v=6.3.0" color="#222">









<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Mist',
    version: '6.3.0',
    sidebar: {"position":"left","display":"hide","offset":12,"b2t":false,"scrollpercent":true,"onmobile":false},
    fancybox: false,
    fastclick: false,
    lazyload: false,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>
  <meta name="description" content="Preface Much round, light burning, ancient people how to learn? Ever fell into fire cannot burn, drowned into the water cannot be die?Just like a mani bead light, impervious halberd knives and swords">
<meta name="keywords" content="Mysql,Database">
<meta property="og:type" content="article">
<meta property="og:title" content="The solution of Databases Query problems(Sixth)">
<meta property="og:url" content="https://absentm.github.io/2016/06/02/The-solution-of-Databases-Query-problems-Sixth/index.html">
<meta property="og:site_name" content="AbsentM&#39;s Notes">
<meta property="og:description" content="Preface Much round, light burning, ancient people how to learn? Ever fell into fire cannot burn, drowned into the water cannot be die?Just like a mani bead light, impervious halberd knives and swords">
<meta property="og:locale" content="en">
<meta property="og:image" content="http://img.blog.csdn.net/20160604144124025">
<meta property="og:image" content="http://img.blog.csdn.net/20160604134750614">
<meta property="og:image" content="http://img.blog.csdn.net/20160604134810099">
<meta property="og:image" content="http://img.blog.csdn.net/20160604135227589">
<meta property="og:image" content="http://img.blog.csdn.net/20160604135557936">
<meta property="og:image" content="http://img.blog.csdn.net/20160604135951456">
<meta property="og:updated_time" content="2017-11-21T11:44:44.748Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="The solution of Databases Query problems(Sixth)">
<meta name="twitter:description" content="Preface Much round, light burning, ancient people how to learn? Ever fell into fire cannot burn, drowned into the water cannot be die?Just like a mani bead light, impervious halberd knives and swords">
<meta name="twitter:image" content="http://img.blog.csdn.net/20160604144124025">






  <link rel="canonical" href="https://absentm.github.io/2016/06/02/The-solution-of-Databases-Query-problems-Sixth/">



<script type="text/javascript" id="page.configurations">
  CONFIG.page = {
    sidebar: "",
  };
</script>

  <title>The solution of Databases Query problems(Sixth) | AbsentM's Notes</title>
  






  <script type="text/javascript">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "https://hm.baidu.com/hm.js?021d64369b3c401c47c6ab7aa56f7074";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>




  <noscript>
  <style type="text/css">
    .use-motion .motion-element,
    .use-motion .brand,
    .use-motion .menu-item,
    .sidebar-inner,
    .use-motion .post-block,
    .use-motion .pagination,
    .use-motion .comments,
    .use-motion .post-header,
    .use-motion .post-body,
    .use-motion .collection-title { opacity: initial; }

    .use-motion .logo,
    .use-motion .site-title,
    .use-motion .site-subtitle {
      opacity: initial;
      top: initial;
    }

    .use-motion {
      .logo-line-before i { left: initial; }
      .logo-line-after i { right: initial; }
    }
  </style>
</noscript>
</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="en">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta">
    

    <div class="custom-logo-site-title">
      <a href="/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">AbsentM's Notes</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
    
  </div>

  <div class="site-nav-toggle">
    <button aria-label="Toggle navigation bar">
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>


<nav class="site-nav">
  
    <ul id="menu" class="menu">
      
        
        
        
          
          <li class="menu-item menu-item-home">
    <a href="/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-home"></i> <br>Home</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-categories">
    <a href="/categories/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-th"></i> <br>Categories</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-archives">
    <a href="/archives/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-archive"></i> <br>Archives</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-tags">
    <a href="/tags/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-tags"></i> <br>Tags</a>
  </li>
        
        
        
          
          <li class="menu-item menu-item-about">
    <a href="/about/" rel="section">
      <i class="menu-item-icon fa fa-fw fa-user"></i> <br>About</a>
  </li>

      
      
        <li class="menu-item menu-item-search">
          
            <a href="javascript:;" class="popup-trigger">
          
            
              <i class="menu-item-icon fa fa-search fa-fw"></i> <br>Search</a>
        </li>
      
    </ul>
  

  
    

  

  
    <div class="site-search">
      
  <div class="popup search-popup local-search-popup">
  <div class="local-search-header clearfix">
    <span class="search-icon">
      <i class="fa fa-search"></i>
    </span>
    <span class="popup-btn-close">
      <i class="fa fa-times-circle"></i>
    </span>
    <div class="local-search-input-wrapper">
      <input autocomplete="off" placeholder="Searching..." spellcheck="false" type="text" id="local-search-input">
    </div>
  </div>
  <div id="local-search-result"></div>
</div>



    </div>
  
</nav>



  



</div>
    </header>

    

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  
    <div class="reading-progress-bar"></div>
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://absentm.github.io/2016/06/02/The-solution-of-Databases-Query-problems-Sixth/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="AbsentM">
      <meta itemprop="description" content="Avenue to Jane!">
      <meta itemprop="image" content="https://raw.githubusercontent.com/absentm/gitRes/master/NotePics/googlrPhoto.png">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="AbsentM's Notes">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">The solution of Databases Query problems(Sixth)
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">

            
            
            

            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">Posted on</span>
              

              
                
              

              <time title="Created: 2016-06-02 12:57:01" itemprop="dateCreated datePublished" datetime="2016-06-02T12:57:01+08:00">2016-06-02</time>
            

            
              

              
                
                <span class="post-meta-divider">|</span>
                

                <span class="post-meta-item-icon">
                  <i class="fa fa-calendar-check-o"></i>
                </span>
                
                  <span class="post-meta-item-text">Edited on</span>
                
                <time title="Modified: 2017-11-21 19:44:44" itemprop="dateModified" datetime="2017-11-21T19:44:44+08:00">2017-11-21</time>
              
            
          </span>

          
            <span class="post-category">
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">In</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing"><a href="/categories/Database-Notes/" itemprop="url" rel="index"><span itemprop="name">Database Notes</span></a></span>

                
                
              
            </span>
          

          
            
              <span class="post-comments-count">
                <span class="post-meta-divider">|</span>
                <span class="post-meta-item-icon">
                  <i class="fa fa-comment-o"></i>
                </span>
                <a href="/2016/06/02/The-solution-of-Databases-Query-problems-Sixth/#comments" itemprop="discussionUrl">
                
                  <span class="post-comments-count disqus-comment-count" data-disqus-identifier="2016/06/02/The-solution-of-Databases-Query-problems-Sixth/" itemprop="commentCount"></span>
                </a>
              </span>
            
          

          
          
             <span id="/2016/06/02/The-solution-of-Databases-Query-problems-Sixth/" class="leancloud_visitors" data-flag-title="The solution of Databases Query problems(Sixth)">
               <span class="post-meta-divider">|</span>
               <span class="post-meta-item-icon">
                 <i class="fa fa-eye"></i>
               </span>
               
                 <span class="post-meta-item-text">Views: </span>
               
                 <span class="leancloud-visitors-count"></span>
             </span>
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <hr>
<h2 id="Preface"><a href="#Preface" class="headerlink" title="Preface"></a>Preface</h2><blockquote>
<p>Much round, light burning, ancient people how to learn? Ever fell into fire cannot burn, drowned into the water cannot be die?<br>Just like a mani bead light, impervious halberd knives and swords.</p>
<p>Can also be kind, also be evil, kind or evil just with him.<br>When kind, can be immortal Buddha; When evil, like a beast. Havoc in Heaven with 72 changes, divine troops descending from Heaven also have no alternative.</p>
</blockquote>
<p><img src="http://img.blog.csdn.net/20160604144124025" alt="kings"></p>
<hr>
<h2 id="Presentation"><a href="#Presentation" class="headerlink" title="Presentation"></a>Presentation</h2><p>In this page, we also will show the homework of the course of the Databases Technology in CMU just like the advance lists of ( Five ). All of the questions we tested in the postgresql.</p>
<p>This section mainly is to practice the query operation of the SQL, include SQL view, index, etc. We also sure that all the sqls will pass in the MySQL, this article also will provide the Chinese version in detail.</p>
<p><a href="https://absentm.github.io/2016/06/04/%E6%95%B0%E6%8D%AE%E5%BA%93%E9%A2%98%E7%9B%AE%E6%95%B4%E7%90%86%E5%8F%8A%E8%AF%A6%E8%A7%A3%E4%B8%AD%E6%96%87%E7%89%88%EF%BC%88%E5%85%AD%EF%BC%89/">中文版</a></p>
<hr>
<h2 id="Question-Details"><a href="#Question-Details" class="headerlink" title="Question Details"></a>Question Details</h2><p>In this question we will use the MovieLens dataset released in May 2011. For more details, please refer to: <a href="http://www.cs.cmu.edu/~christos/courses/dbms.S12/hws/HW2/movielens-readme.txt" target="_blank" rel="noopener">movielens-readme</a></p>
<p>We preprocessed the original dataset and loaded the following two tables to PostgreSQL, this is same to the list of ( Five ):</p>
<p>• movies(mid, title, year, num ratings, rating) , Primary key: mid<br>• play in(mid, name, cast position), Primary key: mid, name</p>
<p>In the table movies, mid is the unique identifier for each movie, title is the movie’s title, and year is the movie’s year-of-release. Each movie receives a total number of num_ratings ratings from users, and the average rating is rating on a scale of 0.0-10.0.</p>
<p>The table play_in contains the main cast of movies. name is the actor’s name (assume each actor has an unique name). cast_position is the order of the actor where he/she appears on the movie cast list (For example, in the movie Titanic, the cast_positions of Leonardo DiCaprio and Kate Winslet are 1 and 2, respectively).</p>
<hr>
<p>We will use Postgres, which is installed in the your own machines.</p>
<p><strong>Queries</strong> </p>
<p>Write SQL queries for the following:</p>
<p><strong>[Q1 – Actor Pool]</strong> Find the total number of distinct actors in the database. <strong>[5 points]</strong></p>
<hr>
<p><strong>[Q2 – Best Rated Motion Pictures]</strong> Among all the movies rated for at least 10,000 times, select the top-10 movies (mid, title, and rating) which receive the highest average rating. <strong>[5 points]</strong></p>
<hr>
<p><strong>[Q3 – Largest Crew]</strong> Return the movie (mid, title) that employed the largest number of actors. If there is a tie, sort the mid’s in ascending order. <strong>[10 points]</strong></p>
<hr>
<p><strong>[Q4 – Active Actor Pool]</strong> Return the total number of active actors in the database. An active actor is one whose most recent movie is released after the year 2006 (i.e. year&gt;2006). <strong>[10 points]</strong></p>
<hr>
<p><strong>[Q5 – Working with Cruise]</strong> Return the actors (name) who has collaborated with Tom Cruise the most of times. If there is a tie, sort the name in ascending order. <strong>[10 points]</strong></p>
<hr>
<p><strong>[Q6 – Working with the New Couple]</strong> Return the actors (name) who have collaborated with both Tom Cruise and Katie Holmes. Sort the output on ascending order of name. <strong>[10 points]</strong></p>
<hr>
<p><strong>[Q7 – The Most Famous Actors]</strong> Find the top-10 active actors (as defined in Q4) whose movies receive the most ratings in total. For example, if Tom Hanks had played in three movies, whose num_ratings are 30, 20, and 10 respectively, then the total number of ratings of his movies would be 30+20+10=60. <strong>[10 points]</strong></p>
<hr>
<p><strong>[Q8 – The Best Actors in a Leading Role]</strong> We define the leading role of each movie as the actors whose cast_position is either 1 or 2 (so the leading role of the movie Titanic is Leonardo DiCaprio and Kate Winslet). Then we say the star quality of each actor is the average rating of the movies in which he/she has played a leading role. For example, if Leonardo DiCaprio had acted in three movies, whose ratings are 7.0, 7.5 and 8.0 respectively, but he only played the first two movies as a leading role, then his star quality would be (7.0 + 7.5) / 2 = 7.25.</p>
<p>Find the actors (name) with top-10 star quality. To reduce noise, the actor must have played as a leading role for at least 5 times. <strong>[10 points]</strong></p>
<hr>
<p><strong>[Q9 – The Breakout Actors in 2008]</strong> Find the number of actors who have acted in more movies in the year 2008 than any other year in their career. <strong>[15 points]</strong></p>
<hr>
<p><strong>[Q10 – Dual Favorite Movies]</strong> One analyst wants to find a set of all-time best movies. He considers two metrics at the same time: its average rating (rating) and the number of ratings it receives (num_ratings). He uses the following rule to determine whether one movie is better than another: For two movies m 1 and m 2 , we define that m 1 dominates m 2 if and only if m 1 has<br>a higher average rating and m 1 receives more ratings.</p>
<p>Find a set of movies (title, num_ratings, rating) M, so that each movie in M is not dominated by any other movie in the database. Return the output on ascending order of title. (FYI: This query is also known as the Skyline Query) <strong>[15 points]</strong></p>
<hr>
<h2 id="Answer"><a href="#Answer" class="headerlink" title="Answer"></a>Answer</h2><p>we give the Postgres version in detail, we will see you can tranfer it easily in mysql or others.</p>
<p>We should create tables of movies and play_in in advance. all the operations we have completed, you can see the refer to: <a href="http://blog.csdn.net/mduanfire/article/details/51405664" target="_blank" rel="noopener">the address: create tables[movies, play_in] and load datas</a> .</p>
<p>Initialization:<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">sudo su - postgres     		# login postgres</span><br><span class="line">psql</span><br><span class="line"></span><br><span class="line">select current_database(); 	# ===&gt; show databases;</span><br><span class="line">\l; 						# list all the databases</span><br><span class="line">\c testdb;					# switch to testdb database;</span><br></pre></td></tr></table></figure></p>
<p>Like this in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160604134750614" alt="0.0"></p>
<p><img src="http://img.blog.csdn.net/20160604134810099" alt="1"></p>
<hr>
<h3 id="Solution-1"><a href="#Solution-1" class="headerlink" title="Solution 1"></a>Solution 1</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT COUNT(DISTINCT name) FROM play_in;</span><br></pre></td></tr></table></figure>
<hr>
<h3 id="Solution-2"><a href="#Solution-2" class="headerlink" title="Solution 2"></a>Solution 2</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT mid, title, rating FROM movies</span><br><span class="line">WHERE num_ratings &gt;= 10000</span><br><span class="line">ORDER BY rating DESC LIMIT 10;</span><br></pre></td></tr></table></figure>
<hr>
<h3 id="Solution-3"><a href="#Solution-3" class="headerlink" title="Solution 3"></a>Solution 3</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW freq</span><br><span class="line">AS</span><br><span class="line">SELECT mid FROM play_in</span><br><span class="line">GROUP BY mid</span><br><span class="line">HAVING COUNT(*) &gt;= ALL (SELECT COUNT(*)</span><br><span class="line">	FROM play_in GROUP BY mid);</span><br><span class="line"></span><br><span class="line">SELECT freq.mid, title FROM freq, movies WHERE freq.mid = movies.mid ORDER BY mid DESC;</span><br></pre></td></tr></table></figure>
<p>The flowing image show the test solution 1, 2, 3 infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160604135227589" alt="123"></p>
<hr>
<h3 id="Solution-4"><a href="#Solution-4" class="headerlink" title="Solution 4"></a>Solution 4</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW latest</span><br><span class="line">AS</span><br><span class="line">SELECT name, max(year) FROM play_in, movies</span><br><span class="line">WHERE play_in.mid = movies.mid</span><br><span class="line">GROUP BY name</span><br><span class="line">HAVING max(year) &gt; 2006;</span><br><span class="line"></span><br><span class="line">SELECT COUNT(*) FROM latest;</span><br></pre></td></tr></table></figure>
<hr>
<h3 id="Solution-5"><a href="#Solution-5" class="headerlink" title="Solution 5"></a>Solution 5</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW cruise_list</span><br><span class="line">AS</span><br><span class="line">SELECT a1.name, COUNT(*) as C FROM play_in AS a1, play_in AS a2</span><br><span class="line">WHERE a1.mid = a2.mid</span><br><span class="line">AND a2.name = &apos;Tom Cruise&apos;</span><br><span class="line">AND a1.name &lt;&gt; &apos;Tom Cruise&apos;</span><br><span class="line">GROUP BY a1.name;</span><br><span class="line"></span><br><span class="line">SELECT name FROM cruise_list where C &gt;= ALL(SELECT C FROM cruise_list) ORDER BY name;</span><br></pre></td></tr></table></figure>
<hr>
<h3 id="Solution-6"><a href="#Solution-6" class="headerlink" title="Solution 6"></a>Solution 6</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">SELECT a1.name FROM play_in AS a1, play_in AS a2</span><br><span class="line">WHERE a1.mid = a2.mid AND a2.name = &apos;Tom Cruise&apos; AND a1.name &lt;&gt; &apos;Tom Cruise&apos;</span><br><span class="line">INTERSECT</span><br><span class="line">SELECT a1.name FROM play_in AS a1, play_in AS a2</span><br><span class="line">WHERE a1.mid = a2.mid AND a2.name = &apos;Katie Holmes&apos; AND a1.name &lt;&gt; &apos;Katie Holmes&apos;</span><br><span class="line">ORDER BY name;</span><br></pre></td></tr></table></figure>
<hr>
<h3 id="Solution-7"><a href="#Solution-7" class="headerlink" title="Solution 7"></a>Solution 7</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">SELECT play_in.name, SUM(num_ratings) </span><br><span class="line">FROM play_in, movies</span><br><span class="line">WHERE play_in.mid = movies.mid</span><br><span class="line">GROUP BY name HAVING MAX(year) &gt; 2006</span><br><span class="line">ORDER BY SUM(num_ratings)</span><br><span class="line">DESC LIMIT 10;</span><br></pre></td></tr></table></figure>
<p>The flowing image show the test solution 4, 5, 6, 7 infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160604135557936" alt="4567"></p>
<hr>
<h3 id="Solution-8"><a href="#Solution-8" class="headerlink" title="Solution 8"></a>Solution 8</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SELECT play_in.name, AVG(movies.rating) </span><br><span class="line">FROM play_in, movies</span><br><span class="line">WHERE cast_position&lt;3 AND play_in.mid = movies.mid</span><br><span class="line">GROUP BY play_in.name</span><br><span class="line">HAVING count(*) &gt;= 5</span><br><span class="line">ORDER BY avg(movies.rating)</span><br><span class="line">DESC LIMIT 10;</span><br></pre></td></tr></table></figure>
<hr>
<h3 id="Solution-9"><a href="#Solution-9" class="headerlink" title="Solution 9"></a>Solution 9</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW helptable(name, year, num_movies) AS</span><br><span class="line">SELECT name, year, count(*) FROM movies AS m, play_in AS p</span><br><span class="line">WHERE m.mid = p.mid</span><br><span class="line">GROUP BY name, year;</span><br><span class="line"></span><br><span class="line">SELECT COUNT(t1.name) FROM helptable AS t1</span><br><span class="line">WHERE t1.year = 2008 AND t1.num_movies &gt; ALL (</span><br><span class="line">SELECT num_movies FROM helptable AS t2</span><br><span class="line">WHERE t1.name = t2.name AND t2.year &lt;&gt; 2008);</span><br></pre></td></tr></table></figure>
<hr>
<h3 id="Solution-10"><a href="#Solution-10" class="headerlink" title="Solution 10"></a>Solution 10</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SELECT title, num_ratings, rating FROM movies</span><br><span class="line">WHERE mid NOT IN</span><br><span class="line">(SELECT a2.mid FROM movies AS a1, movies AS a2</span><br><span class="line">WHERE a1.mid&lt;&gt;a2.mid</span><br><span class="line">AND a1.num_ratings &gt; a2.num_ratings</span><br><span class="line">AND a1.rating &gt; a2.rating)</span><br><span class="line">ORDER BY title;</span><br></pre></td></tr></table></figure>
<p>The flowing image show the test solution 8, 9, 10 infos in my ubuntu os:</p>
<p><img src="http://img.blog.csdn.net/20160604135951456" alt="8910"></p>
<hr>
<h2 id="Reference"><a href="#Reference" class="headerlink" title="Reference"></a>Reference</h2><p>[1]. <a href="http://www.cs.cmu.edu/~christos/courses/dbms.S12/hws/HW2/PostgreSQLReadme.htm" target="_blank" rel="noopener">http://www.cs.cmu.edu/~christos/courses/dbms.S12/hws/HW2/PostgreSQLReadme.htm</a><br>[2]. <a href="http://blog.chinaunix.net/uid-26642180-id-3485465.html" target="_blank" rel="noopener">http://blog.chinaunix.net/uid-26642180-id-3485465.html</a><br>[3]. <a href="http://www.cnblogs.com/gaojian/archive/2013/07/04/3170989.html" target="_blank" rel="noopener">http://www.cnblogs.com/gaojian/archive/2013/07/04/3170989.html</a></p>
      
    </div>

    

    
    
    

    

    
      <div>
        <div style="padding: 10px 0; margin: 20px auto; width: 90%; text-align: center">
  <div>Enjoy it ? Donate me !  求鼓励，求支持！</div>
  <button id="rewardButton" disable="enable" onclick="var qr = document.getElementById('QR'); if (qr.style.display === 'none') {qr.style.display='block';} else {qr.style.display='none'}">
    <span>Donate</span>
  </button>
  <div id="QR" style="display: none">

    
      <div id="wechat" style="display: inline-block">
        <img id="wechat_qr" src="https://raw.githubusercontent.com/absentm/gitRes/master/NotePics/wechat.png" alt="AbsentM WeChat Pay">
        <p>WeChat Pay</p>
      </div>
    

    
      <div id="alipay" style="display: inline-block">
        <img id="alipay_qr" src="https://raw.githubusercontent.com/absentm/gitRes/master/NotePics/alipay.png" alt="AbsentM Alipay">
        <p>Alipay</p>
      </div>
    

    

  </div>
</div>

      </div>
    

    

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/Mysql/" rel="tag"># Mysql</a>
          
            <a href="/tags/Database/" rel="tag"># Database</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/2016/06/01/转-常用的Chrome插件推荐/" rel="next" title="转 - 常用的 Chrome 插件推荐">
                <i class="fa fa-chevron-left"></i> 转 - 常用的 Chrome 插件推荐
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/2016/06/04/数据库题目整理及详解中文版（六）/" rel="prev" title="数据库题目整理及详解中文版（六）">
                数据库题目整理及详解中文版（六） <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>


  </div>


          </div>
          

  
    <div class="comments" id="comments">
      <div id="disqus_thread">
        <noscript>
          Please enable JavaScript to view the
          <a href="https://disqus.com/?ref_noscript">comments powered by Disqus.</a>
        </noscript>
      </div>
    </div>

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            Table of Contents
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            Overview
          </li>
        </ul>
      

      <section class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <img class="site-author-image" itemprop="image" src="https://raw.githubusercontent.com/absentm/gitRes/master/NotePics/googlrPhoto.png" alt="AbsentM">
            
              <p class="site-author-name" itemprop="name">AbsentM</p>
              <p class="site-description motion-element" itemprop="description">Avenue to Jane!</p>
          </div>

          
            <nav class="site-state motion-element">
              
                <div class="site-state-item site-state-posts">
                
                  <a href="/archives/">
                
                    <span class="site-state-item-count">57</span>
                    <span class="site-state-item-name">posts</span>
                  </a>
                </div>
              

              
                
                
                <div class="site-state-item site-state-categories">
                  <a href="/categories/index.html">
                    
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                    <span class="site-state-item-count">8</span>
                    <span class="site-state-item-name">categories</span>
                  </a>
                </div>
              

              
                
                
                <div class="site-state-item site-state-tags">
                  <a href="/tags/index.html">
                    
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                    <span class="site-state-item-count">37</span>
                    <span class="site-state-item-name">tags</span>
                  </a>
                </div>
              
            </nav>
          

          

          
            <div class="links-of-author motion-element">
              
                <span class="links-of-author-item">
                  <a href="https://github.com/absentm" target="_blank" title="GitHub"><i class="fa fa-fw fa-github"></i>GitHub</a>
                  
                </span>
              
                <span class="links-of-author-item">
                  <a href="https://twitter.com/absentm123" target="_blank" title="Twitter"><i class="fa fa-fw fa-twitter"></i>Twitter</a>
                  
                </span>
              
                <span class="links-of-author-item">
                  <a href="http://weibo.com/dmicrocosmos" target="_blank" title="Weibo"><i class="fa fa-fw fa-weibo"></i>Weibo</a>
                  
                </span>
              
            </div>
          

          
          

          
          
            <div class="links-of-blogroll motion-element links-of-blogroll-inline">
              <div class="links-of-blogroll-title">
                <i class="fa fa-fw fa-link"></i>
                Links
              </div>
              <ul class="links-of-blogroll-list">
                
                  <li class="links-of-blogroll-item">
                    <a href="https://www.hanhanfilm.com/" title="憨憨电影" target="_blank">憨憨电影</a>
                  </li>
                
                  <li class="links-of-blogroll-item">
                    <a href="http://www.fengxiaochuang.com/" title="晓闯博客" target="_blank">晓闯博客</a>
                  </li>
                
              </ul>
            </div>
          

          
            
          
          

        </div>
      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#Preface"><span class="nav-number">1.</span> <span class="nav-text">Preface</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Presentation"><span class="nav-number">2.</span> <span class="nav-text">Presentation</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Question-Details"><span class="nav-number">3.</span> <span class="nav-text">Question Details</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Answer"><span class="nav-number">4.</span> <span class="nav-text">Answer</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-1"><span class="nav-number">4.1.</span> <span class="nav-text">Solution 1</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-2"><span class="nav-number">4.2.</span> <span class="nav-text">Solution 2</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-3"><span class="nav-number">4.3.</span> <span class="nav-text">Solution 3</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-4"><span class="nav-number">4.4.</span> <span class="nav-text">Solution 4</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-5"><span class="nav-number">4.5.</span> <span class="nav-text">Solution 5</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-6"><span class="nav-number">4.6.</span> <span class="nav-text">Solution 6</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-7"><span class="nav-number">4.7.</span> <span class="nav-text">Solution 7</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-8"><span class="nav-number">4.8.</span> <span class="nav-text">Solution 8</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-9"><span class="nav-number">4.9.</span> <span class="nav-text">Solution 9</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Solution-10"><span class="nav-number">4.10.</span> <span class="nav-text">Solution 10</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Reference"><span class="nav-number">5.</span> <span class="nav-text">Reference</span></a></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; 2015 – <span itemprop="copyrightYear">2019</span>
  <span class="with-love" id="animate">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">AbsentM</span>

  

  
</div>




  <div class="powered-by">Powered by <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a> v3.7.1</div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">Theme – <a class="theme-link" target="_blank" href="https://theme-next.org">NexT.Mist</a> v6.3.0</div>
        
<div class="busuanzi-count">
  <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>

  
    <span class="site-uv" title="Total Visitors">
      <i class="fa fa-user"></i>
      <span class="busuanzi-value" id="busuanzi_value_site_uv"></span>
    </span>
  

  
    <span class="site-pv" title="Total Views">
      <i class="fa fa-eye"></i>
      <span class="busuanzi-value" id="busuanzi_value_site_pv"></span>
    </span>
  
</div>









        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
          <span id="scrollpercent"><span>0</span>%</span>
        
      </div>
    

    
	
    

    
      <div>
        <!-- Go to www.addthis.com/dashboard to customize your tools -->
<div class="addthis_inline_share_toolbox">
  <script type="text/javascript" src="//s7.addthis.com/js/300/addthis_widget.js#pubid=ra-58d66725c4aec37d" async="async"></script>
</div>

      </div>
    
  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>
























  



  
  
    <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/reading_progress/reading_progress.js"></script>
  


  


  <script type="text/javascript" src="/js/src/utils.js?v=6.3.0"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=6.3.0"></script>



  
  

  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=6.3.0"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=6.3.0"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=6.3.0"></script>



  

  
    <script id="dsq-count-scr" src="https://absentm.disqus.com/count.js" async></script>
  

  
    <script type="text/javascript">
      var disqus_config = function () {
        this.page.url = 'https://absentm.github.io/2016/06/02/The-solution-of-Databases-Query-problems-Sixth/';
        this.page.identifier = '2016/06/02/The-solution-of-Databases-Query-problems-Sixth/';
        this.page.title = 'The solution of Databases Query problems(Sixth)';
        };
      function loadComments () {
        var d = document, s = d.createElement('script');
        s.src = 'https://absentm.disqus.com/embed.js';
        s.setAttribute('data-timestamp', '' + +new Date());
        (d.head || d.body).appendChild(s);
      }
      
        $(function () {
          var offsetTop = $('#comments').offset().top - $(window).height();
          if (offsetTop <= 0) {
            // load directly when there's no a scrollbar
            loadComments();
          } else {
            $(window).on('scroll.disqus_scroll', function () {
              var scrollTop = document.documentElement.scrollTop;
              if (scrollTop >= offsetTop) {
                $(window).off('.disqus_scroll');
                loadComments();
              }
            });
          }
        });
      
    </script>
  












  

  <script type="text/javascript">
    // Popup Window;
    var isfetched = false;
    var isXml = true;
    // Search DB path;
    var search_path = "search.xml";
    if (search_path.length === 0) {
      search_path = "search.xml";
    } else if (/json$/i.test(search_path)) {
      isXml = false;
    }
    var path = "/" + search_path;
    // monitor main search box;

    var onPopupClose = function (e) {
      $('.popup').hide();
      $('#local-search-input').val('');
      $('.search-result-list').remove();
      $('#no-result').remove();
      $(".local-search-pop-overlay").remove();
      $('body').css('overflow', '');
    }

    function proceedsearch() {
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay"></div>')
        .css('overflow', 'hidden');
      $('.search-popup-overlay').click(onPopupClose);
      $('.popup').toggle();
      var $localSearchInput = $('#local-search-input');
      $localSearchInput.attr("autocapitalize", "none");
      $localSearchInput.attr("autocorrect", "off");
      $localSearchInput.focus();
    }

    // search function;
    var searchFunc = function(path, search_id, content_id) {
      'use strict';

      // start loading animation
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay">' +
          '<div id="search-loading-icon">' +
          '<i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>' +
          '</div>' +
          '</div>')
        .css('overflow', 'hidden');
      $("#search-loading-icon").css('margin', '20% auto 0 auto').css('text-align', 'center');

      

      $.ajax({
        url: path,
        dataType: isXml ? "xml" : "json",
        async: true,
        success: function(res) {
          // get the contents from search data
          isfetched = true;
          $('.popup').detach().appendTo('.header-inner');
          var datas = isXml ? $("entry", res).map(function() {
            return {
              title: $("title", this).text(),
              content: $("content",this).text(),
              url: $("url" , this).text()
            };
          }).get() : res;
          var input = document.getElementById(search_id);
          var resultContent = document.getElementById(content_id);
          var inputEventFunction = function() {
            var searchText = input.value.trim().toLowerCase();
            var keywords = searchText.split(/[\s\-]+/);
            if (keywords.length > 1) {
              keywords.push(searchText);
            }
            var resultItems = [];
            if (searchText.length > 0) {
              // perform local searching
              datas.forEach(function(data) {
                var isMatch = false;
                var hitCount = 0;
                var searchTextCount = 0;
                var title = data.title.trim();
                var titleInLowerCase = title.toLowerCase();
                var content = data.content.trim().replace(/<[^>]+>/g,"");
                
                var contentInLowerCase = content.toLowerCase();
                var articleUrl = decodeURIComponent(data.url);
                var indexOfTitle = [];
                var indexOfContent = [];
                // only match articles with not empty titles
                if(title != '') {
                  keywords.forEach(function(keyword) {
                    function getIndexByWord(word, text, caseSensitive) {
                      var wordLen = word.length;
                      if (wordLen === 0) {
                        return [];
                      }
                      var startPosition = 0, position = [], index = [];
                      if (!caseSensitive) {
                        text = text.toLowerCase();
                        word = word.toLowerCase();
                      }
                      while ((position = text.indexOf(word, startPosition)) > -1) {
                        index.push({position: position, word: word});
                        startPosition = position + wordLen;
                      }
                      return index;
                    }

                    indexOfTitle = indexOfTitle.concat(getIndexByWord(keyword, titleInLowerCase, false));
                    indexOfContent = indexOfContent.concat(getIndexByWord(keyword, contentInLowerCase, false));
                  });
                  if (indexOfTitle.length > 0 || indexOfContent.length > 0) {
                    isMatch = true;
                    hitCount = indexOfTitle.length + indexOfContent.length;
                  }
                }

                // show search results

                if (isMatch) {
                  // sort index by position of keyword

                  [indexOfTitle, indexOfContent].forEach(function (index) {
                    index.sort(function (itemLeft, itemRight) {
                      if (itemRight.position !== itemLeft.position) {
                        return itemRight.position - itemLeft.position;
                      } else {
                        return itemLeft.word.length - itemRight.word.length;
                      }
                    });
                  });

                  // merge hits into slices

                  function mergeIntoSlice(text, start, end, index) {
                    var item = index[index.length - 1];
                    var position = item.position;
                    var word = item.word;
                    var hits = [];
                    var searchTextCountInSlice = 0;
                    while (position + word.length <= end && index.length != 0) {
                      if (word === searchText) {
                        searchTextCountInSlice++;
                      }
                      hits.push({position: position, length: word.length});
                      var wordEnd = position + word.length;

                      // move to next position of hit

                      index.pop();
                      while (index.length != 0) {
                        item = index[index.length - 1];
                        position = item.position;
                        word = item.word;
                        if (wordEnd > position) {
                          index.pop();
                        } else {
                          break;
                        }
                      }
                    }
                    searchTextCount += searchTextCountInSlice;
                    return {
                      hits: hits,
                      start: start,
                      end: end,
                      searchTextCount: searchTextCountInSlice
                    };
                  }

                  var slicesOfTitle = [];
                  if (indexOfTitle.length != 0) {
                    slicesOfTitle.push(mergeIntoSlice(title, 0, title.length, indexOfTitle));
                  }

                  var slicesOfContent = [];
                  while (indexOfContent.length != 0) {
                    var item = indexOfContent[indexOfContent.length - 1];
                    var position = item.position;
                    var word = item.word;
                    // cut out 100 characters
                    var start = position - 20;
                    var end = position + 80;
                    if(start < 0){
                      start = 0;
                    }
                    if (end < position + word.length) {
                      end = position + word.length;
                    }
                    if(end > content.length){
                      end = content.length;
                    }
                    slicesOfContent.push(mergeIntoSlice(content, start, end, indexOfContent));
                  }

                  // sort slices in content by search text's count and hits' count

                  slicesOfContent.sort(function (sliceLeft, sliceRight) {
                    if (sliceLeft.searchTextCount !== sliceRight.searchTextCount) {
                      return sliceRight.searchTextCount - sliceLeft.searchTextCount;
                    } else if (sliceLeft.hits.length !== sliceRight.hits.length) {
                      return sliceRight.hits.length - sliceLeft.hits.length;
                    } else {
                      return sliceLeft.start - sliceRight.start;
                    }
                  });

                  // select top N slices in content

                  var upperBound = parseInt('1');
                  if (upperBound >= 0) {
                    slicesOfContent = slicesOfContent.slice(0, upperBound);
                  }

                  // highlight title and content

                  function highlightKeyword(text, slice) {
                    var result = '';
                    var prevEnd = slice.start;
                    slice.hits.forEach(function (hit) {
                      result += text.substring(prevEnd, hit.position);
                      var end = hit.position + hit.length;
                      result += '<b class="search-keyword">' + text.substring(hit.position, end) + '</b>';
                      prevEnd = end;
                    });
                    result += text.substring(prevEnd, slice.end);
                    return result;
                  }

                  var resultItem = '';

                  if (slicesOfTitle.length != 0) {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</a>";
                  } else {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + title + "</a>";
                  }

                  slicesOfContent.forEach(function (slice) {
                    resultItem += "<a href='" + articleUrl + "'>" +
                      "<p class=\"search-result\">" + highlightKeyword(content, slice) +
                      "...</p>" + "</a>";
                  });

                  resultItem += "</li>";
                  resultItems.push({
                    item: resultItem,
                    searchTextCount: searchTextCount,
                    hitCount: hitCount,
                    id: resultItems.length
                  });
                }
              })
            };
            if (keywords.length === 1 && keywords[0] === "") {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-search fa-5x" /></div>'
            } else if (resultItems.length === 0) {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>'
            } else {
              resultItems.sort(function (resultLeft, resultRight) {
                if (resultLeft.searchTextCount !== resultRight.searchTextCount) {
                  return resultRight.searchTextCount - resultLeft.searchTextCount;
                } else if (resultLeft.hitCount !== resultRight.hitCount) {
                  return resultRight.hitCount - resultLeft.hitCount;
                } else {
                  return resultRight.id - resultLeft.id;
                }
              });
              var searchResultList = '<ul class=\"search-result-list\">';
              resultItems.forEach(function (result) {
                searchResultList += result.item;
              })
              searchResultList += "</ul>";
              resultContent.innerHTML = searchResultList;
            }
          }

          if ('auto' === 'auto') {
            input.addEventListener('input', inputEventFunction);
          } else {
            $('.search-icon').click(inputEventFunction);
            input.addEventListener('keypress', function (event) {
              if (event.keyCode === 13) {
                inputEventFunction();
              }
            });
          }

          // remove loading animation
          $(".local-search-pop-overlay").remove();
          $('body').css('overflow', '');

          proceedsearch();
        }
      });
    }

    // handle and trigger popup window;
    $('.popup-trigger').click(function(e) {
      e.stopPropagation();
      if (isfetched === false) {
        searchFunc(path, 'local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };
    });

    $('.popup-btn-close').click(onPopupClose);
    $('.popup').click(function(e){
      e.stopPropagation();
    });
    $(document).on('keyup', function (event) {
      var shouldDismissSearchPopup = event.which === 27 &&
        $('.search-popup').is(':visible');
      if (shouldDismissSearchPopup) {
        onPopupClose();
      }
    });
  </script>





  

  
  <script src="https://cdn1.lncld.net/static/js/av-core-mini-0.6.4.js"></script>
  <script>AV.initialize("8gIMbJWMH7XprqgkEiPWXvuu-gzGzoHsz", "W9NRpyhllWph45XiJsEHVgct");</script>
  <script>
    function showTime(Counter) {
      var query = new AV.Query(Counter);
      var entries = [];
      var $visitors = $(".leancloud_visitors");

      $visitors.each(function () {
        entries.push( $(this).attr("id").trim() );
      });

      query.containedIn('url', entries);
      query.find()
        .done(function (results) {
          var COUNT_CONTAINER_REF = '.leancloud-visitors-count';

          if (results.length === 0) {
            $visitors.find(COUNT_CONTAINER_REF).text(0);
            return;
          }

          for (var i = 0; i < results.length; i++) {
            var item = results[i];
            var url = item.get('url');
            var time = item.get('time');
            var element = document.getElementById(url);

            $(element).find(COUNT_CONTAINER_REF).text(time);
          }
          for(var i = 0; i < entries.length; i++) {
            var url = entries[i];
            var element = document.getElementById(url);
            var countSpan = $(element).find(COUNT_CONTAINER_REF);
            if( countSpan.text() == '') {
              countSpan.text(0);
            }
          }
        })
        .fail(function (object, error) {
          console.log("Error: " + error.code + " " + error.message);
        });
    }

    function addCount(Counter) {
      var $visitors = $(".leancloud_visitors");
      var url = $visitors.attr('id').trim();
      var title = $visitors.attr('data-flag-title').trim();
      var query = new AV.Query(Counter);

      query.equalTo("url", url);
      query.find({
        success: function(results) {
          if (results.length > 0) {
            var counter = results[0];
            counter.fetchWhenSave(true);
            counter.increment("time");
            
            counter.save(null, {
              success: function(counter) {
                
                  var $element = $(document.getElementById(url));
                  $element.find('.leancloud-visitors-count').text(counter.get('time'));
                
              },
              error: function(counter, error) {
                console.log('Failed to save Visitor num, with error message: ' + error.message);
              }
            });
          } else {
            
              var newcounter = new Counter();
              /* Set ACL */
              var acl = new AV.ACL();
              acl.setPublicReadAccess(true);
              acl.setPublicWriteAccess(true);
              newcounter.setACL(acl);
              /* End Set ACL */
              newcounter.set("title", title);
              newcounter.set("url", url);
              newcounter.set("time", 1);
              newcounter.save(null, {
                success: function(newcounter) {
                  var $element = $(document.getElementById(url));
                  $element.find('.leancloud-visitors-count').text(newcounter.get('time'));
                },
                error: function(newcounter, error) {
                  console.log('Failed to create');
                }
              });
            
          }
        },
        error: function(error) {
          console.log('Error:' + error.code + " " + error.message);
        }
      });
    }

    $(function() {
      var Counter = AV.Object.extend("Counter");
      if ($('.leancloud_visitors').length == 1) {
        addCount(Counter);
      } else if ($('.post-title-link').length > 1) {
        showTime(Counter);
      }
    });
  </script>



  

  
<script>
(function(){
    var bp = document.createElement('script');
    var curProtocol = window.location.protocol.split(':')[0];
    if (curProtocol === 'https') {
        bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';        
    }
    else {
        bp.src = 'http://push.zhanzhang.baidu.com/push.js';
    }
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(bp, s);
})();
</script>


  
  

  
  

  


  
  

  

  

  

  

  

</body>
</html>